Synopsis: Index Shotgun

“Hey! Got a minute? I could use your help,” the Oklahoman accent on the phone is shouting over the data center ventilation. It’s the lead database administrator for your company.

“Sure,” you answer, a little unsure about what they could want.

“The thing is, you’ve got a database here that’s pretty much taken over the server,” the DBA continues. “I went in there to take a look, and I see the problem. You’ve got no indexes on some tables and every index in the world on some other tables. We’ve got to get this worked out or give you a server all to yourself because nobody else can get any time!”

“I’m sorry, I don’t know that much about databases,” you reply, trying to calm down the DBA. “We did our best to guess at the optimization, but that’s what an expert like you can do. Isn’t there some database tuning you can do?”

“I tuned everything I could. That’s why we’re still running down here at all,” the DBA answers. “The only option left is to throttle your app, and I don’t think you want that. We’ve got to stop guessing and start getting some answers on what your app needs the database to do.”

You can tell this is passing over your head. Warily you ask, “What do you have in mind? I told you, we don’t have expert database knowledge in our team.”

“That’s no problem,” the DBA laughs. “You do know your application. That’s the part that counts — and the part I can’t help with. I’ll get one of my support team members to set you up with the right tools, and then we’ll fix your bottleneck. You need a little mentoring. You’ll see.”

Objective: Optimize performance#

Performance is the single most common concern that one hears from database developers. It is enough to look at the talks scheduled at any technical conference to see this: they’re all full of tools and techniques to squeeze more work out of our databases. It is not surprising that when a programmer tries to talk about a way to structure a database or to write SQL to give better reliability, security, or correctness, the only question they get from the audience is, “OK, but how does that affect performance?”

The best technique for improving performance in our database is to use indexes well. An index is a data structure that the database uses to correlate values to the rows where these values occur in a given column. An index provides an easy way for the database to find values more quickly than the brute-force method of searching the whole table from top to bottom.

Software developers often don’t understand how or when to use an index. Documentation and books about databases rarely contain a clear guide for when to use an index. Developers can only guess how to use indexes effectively.

Legitimate uses of the antipattern#

If we need to design a database for general use without knowing what queries are important to optimize, we can’t be sure about which indexes are best. We have to make an educated guess. It is likely that we miss some indexes that could have been beneficial. It’s also likely that we create some indexes that turn out to be unneeded. Regardless, we have to make the best guess we can.